﻿--update persons set password = null WHERE CanReceive=0
update persons set location = 'Station' WHERE Location='ToEnter'


select cast((Abs(Checksum(NewId()))%10) as varchar(1)) + 
       char(ascii('a')+(Abs(Checksum(NewId()))%25)) +
       char(ascii('A')+(Abs(Checksum(NewId()))%25)) +
       left(newid(),5)
from persons

select * from persons

select product,limitedqty,sum(qty) bought,(limitedqty - SUM(qty)) from orders inner join products on products.productid = orders.productid
group by product,limitedqty

select * from orders

print getdate()

update orders set paiddatetime = null where paid = 0

SELECT     O.OrderID, Pr. Product, Pr.Description, Pr.ImageURL, O.Qty, O.DeliveryDate, O.SentFrom, O.Note, Pr.Price, Pr.Price * O.Qty AS SubTotal, O.SenderID, 
                      RN.SenderName AS RecipientName, 'Ready for Delivery - Your account will be billed.' AS OrderStatus, Pr.SimpleName, SN.RVAID, O.PaidDateTime, OtherTo, 
                      OrderDateTime, Paid
FROM         dbo.Orders AS O INNER JOIN
                      dbo.SenderNameID AS SN ON O.SenderID = SN.PersonID INNER JOIN
                      dbo.Products AS Pr ON O.ProductID = Pr.ProductID INNER JOIN
                      dbo.SenderNameID AS RN ON O.ReceiverID = RN.PersonID
WHERE     (SN.IsStudent = 0) AND (Pr.LimitedQty > - 2)

select * from Persons where Email = 'tchase@rva.org'

update Persons set Pname = '' where Pname is null
select * from Persons where Pname is null

update Persons set password = cast((Abs(Checksum(NewId()))%10) as varchar(1)) + 
       char(ascii('a')+(Abs(Checksum(NewId()))%25)) +
       char(ascii('A')+(Abs(Checksum(NewId()))%25)) +
       left(newid(),5) where CanReceive = 1 and Password IS NULL
       
select * from Persons where CanReceive = 1 and password is null
select * from Persons where Email='******'


select * from Persons where Email='*****'
select * from Persons where Lname='OTHER'

--Movie Lover's Basket
--American Candy Mug
select * from orders inner join Persons on Orders.SenderID = Persons.PersonID where IsStudent = 1

select * from orders inner join Persons on Orders.SenderID = Persons.PersonID where ProductID = 17 or ProductID = 19

17 - movie lover
19 - american candy
New numbers:
51 - lights popcorn action
50 - american sweet tooth

select * From products

select * from orders inner join Persons on Orders.SenderID = Persons.PersonID 
where Email='******'

select SUM(Qty) From orders where ProductID = 50

select * from Persons where Email = '******'

select * from orderlist

select sum(charge) from (SELECT [OrderID], RVAID as [RVA ID], Fname as [First Name], Lname as [Last Name],
	Product as [Product Name], Price*QTY AS Charge,[OrderDateTime]
FROM ([Orders] INNER JOIN Products ON Orders.ProductID = Products.ProductID) 
			   INNER JOIN Persons ON Persons.PersonID = Orders.SenderID ) as r

select * from persons order by rvaid

SELECT [OrderID], RVAID as [RVA ID], Fname as [First Name], Lname as [Last Name],
	Product as [Product Name], Price*QTY AS Charge,[OrderDateTime]
FROM ([Orders] INNER JOIN Products ON Orders.ProductID = Products.ProductID) 
			   INNER JOIN Persons ON Persons.PersonID = Orders.SenderID 
WHERE (ISStudent = 0) 
ORDER BY RVAID
